
/*==============================================================*/
/* Table: {organ.code}_FILE                                             */
/*==============================================================*/
CREATE TABLE {organ.code}_FILE  (
   ID                   INTEGER                    NOT NULL,
   LOGIC_ID             INTEGER                    NOT NULL,
   ORGAN_LOGIC_ID       INTEGER,
   OBJ_TYPE             NVARCHAR2(50),
   OBJ_LOGIC_ID         INTEGER                    NOT NULL,
   OLD_FILE_NAME        NVARCHAR2(100),
   NEW_FILE_NAME        NVARCHAR2(50),
   FILE_LENGTH          NUMBER(20,0),
   FILE_TYPE            NVARCHAR2(20),
   PATH                 NVARCHAR2(100),
   FILE_ORDER           INTEGER,
   TEMP_ID              INTEGER,
   DOC_TYPE             NVARCHAR2(50),
   FILE_URL             NVARCHAR2(100),
   USER_ID              INTEGER,
   CREATE_TIME          DATE,
   EXT1                 NVARCHAR2(50),
   EXT2                 NVARCHAR2(50),
   EXT3                 NVARCHAR2(50),
   EXT4                 NVARCHAR2(50),
   EXT5                 NVARCHAR2(50),
   CONSTRAINT PK_{organ.code}_FILE PRIMARY KEY (ID)
);

/*==============================================================*/
/* Table: {organ.code}_FILE_DOWN                                        */
/*==============================================================*/
CREATE TABLE {organ.code}_FILE_DOWN  (
   ID                   NUMBER(6)                       NOT NULL,
   ORGAN_LOGIC_ID       INTEGER,
   FILE_LOGIC_ID        INTEGER                    NOT NULL,
   OBJ_LOGIC_ID         INTEGER,
   DOWNLOAD_TIME        DATE,
   MAC                  NVARCHAR2(50),
   IP                   NVARCHAR2(50),
   EXT1                 NVARCHAR2(50),
   EXT2                 NVARCHAR2(50),
   EXT3                 NVARCHAR2(50),
   EXT4                 NVARCHAR2(50),
   EXT5                 NVARCHAR2(50),
   CONSTRAINT PK_{organ.code}_FILE_DOWN PRIMARY KEY (ID)
);

/*==============================================================*/
/* Table: {organ.code}_FILE_LOG                                         */
/*==============================================================*/
CREATE TABLE {organ.code}_FILE_LOG  (
   ID                   NUMBER(6)                       NOT NULL,
   ORGAN_LOGIC_ID       INTEGER,
   FILE_LOGIC_ID        INTEGER                    NOT NULL,
   OBJ_LOGIC_ID         INTEGER,
   LOG_TIME             DATE,
   OLD_FILE_NAME        NVARCHAR2(100),
   NEW_FILE_NAME        NVARCHAR2(50),
   FILE_LENGTH          NUMBER(20,0),
   FILE_TYPE            NVARCHAR2(20),
   PATH                 NVARCHAR2(100),
   FILE_ORDER           INTEGER,
   TEMP_ID              INTEGER,
   DOC_TYPE             NVARCHAR2(50),
   FILE_URL             NVARCHAR2(100),
   USER_ID              INTEGER,
   CREATE_TIME          DATE,
   EXT1                 NVARCHAR2(50),
   EXT2                 NVARCHAR2(50),
   EXT3                 NVARCHAR2(50),
   EXT4                 NVARCHAR2(50),
   EXT5                 NVARCHAR2(50),
   CONSTRAINT PK_{organ.code}_FILE_LOG PRIMARY KEY (ID)
);

CREATE TRIGGER "TIB_{organ.code}_FILE_DOWN" BEFORE INSERT
ON {organ.code}_FILE_DOWN FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE S_{organ.code}_FILE_DOWN
    SELECT S_{organ.code}_FILE_DOWN.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;

CREATE TRIGGER "TIB_{organ.code}_FILE_LOG" BEFORE INSERT
ON {organ.code}_FILE_LOG FOR EACH ROW
DECLARE
    INTEGRITY_ERROR  EXCEPTION;
    ERRNO            INTEGER;
    ERRMSG           CHAR(200);
    DUMMY            INTEGER;
    FOUND            BOOLEAN;

BEGIN
    --  COLUMN "ID" USES SEQUENCE S_{organ.code}_FILE_LOG
    SELECT S_{organ.code}_FILE_LOG.NEXTVAL INTO :NEW.ID FROM DUAL;

--  ERRORS HANDLING
EXCEPTION
    WHEN INTEGRITY_ERROR THEN
       RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
